package com.securecrt.pojo;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME="elissh.db";
	private static final int DATABASE_VERSION=1;
	//table SESSION
	public static final String SESSION="sessions";
	//columns table SESSION
	public static final String SESSION_ID="session_id";
	public static final String HOSTNAME="hostname";
	public static final String PORT="port";
	public static final String USERNAME="username";
	public static final String PASSWORD="password";
	public static final String TIME_="time_";
	
	//detail table SETTINGS
	public static final String SETTINGS="settings";
	//columns detail table SETTINGS
	public static final String SETTING_ID="setting_id";
	public static final String SET_SESSION_ID="set_session_id";
	public static final String BACKGROUND="background";
	public static final String FONTSIZE="fontsize";
	//table setting main
	public static final String SETTINGS_MAIN="settings_main";
	//columns setting main
	public static final String SETTINGS_MAIN_ID="settings_main_id";
	public static final String BACKGROUND_="background_";
	public static final String LANGUAGE_="language_";
	
	//table REM_PASS
	public static final String REM_PASS="rem_pass";
	//columns REM_PASS
	public static final String REM_PASS_ID="rem_pass_id";
	public static final String REMPASS_SESSION_ID="rempass_session_id";
	public static final String CKREM="ckrem";
	
	public SQLHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql="create table "+SESSION+"( "+SESSION_ID+" integer primary key, "+HOSTNAME+" text null, "+PORT+" text null, "
			+USERNAME+" text null,"+PASSWORD+" text null,"+TIME_+" text null);";
		Log.d("EventsData", "onCreate: "+sql);
		db.execSQL(sql);
		db.execSQL("create table "+SETTINGS+"( "+SETTING_ID+" integer primary key autoincrement, "+SET_SESSION_ID+" integer not null, "+BACKGROUND+" text null, "
				+FONTSIZE+" text null);");
		db.execSQL("create table "+SETTINGS_MAIN+"( "+SETTINGS_MAIN_ID+" integer primary key autoincrement, "+BACKGROUND_+" text null, "+LANGUAGE_+" text null);");
		db.execSQL("create table "+REM_PASS+"( "+REM_PASS_ID+" integer primary key autoincrement, "+REMPASS_SESSION_ID+" integer not null, "+CKREM+" text null);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(oldVersion>=newVersion)
			return;
		String sql=null;
		if(oldVersion==1)
			sql="alter table "+SESSION+" add not text;";
		if(oldVersion==2)
			sql="";
		if(sql!=null)
			db.execSQL(sql);
		db.execSQL("drop table if exists "+SETTINGS);
		db.execSQL("drop table if exists "+SETTINGS_MAIN);
		db.execSQL("drop table if exists "+SESSION);
		db.execSQL("drop table if exists "+REM_PASS);
		onCreate(db);
	}
	public boolean insertSaveSession(Long id,String host,String port,String user,String pass,String cuDate){
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues values=new ContentValues();
		values.put(SESSION_ID, id);
		values.put(HOSTNAME, host);
		values.put(PORT, port);
		values.put(USERNAME, String.valueOf(user));
		values.put(PASSWORD, pass);
		values.put(TIME_, cuDate);
		return (db.insert(SESSION, null, values)>0);
	}
	public boolean insertSettingMain(int bg,String lang){
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues values=new ContentValues();
		values.put(BACKGROUND_, bg);
		values.put(LANGUAGE_, lang);
		return (db.insert(SETTINGS_MAIN, null, values)>0);
	}
	
	public boolean insertSetting(Long ssid,int bg,int fz){
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues values=new ContentValues();
		values.put(SET_SESSION_ID, ssid);
		values.put(BACKGROUND, bg);
		values.put(FONTSIZE, fz);
		return (db.insert(SETTINGS, null, values)>0);
	}
	
	public boolean insertRemPass(Long ssid, String ck){System.out.println(ssid+"----ins----"+ck);
		SQLiteDatabase db=this.getReadableDatabase();
		ContentValues values=new ContentValues();
		values.put(REMPASS_SESSION_ID, ssid);
		values.put(CKREM, ck);
		return (db.insert(REM_PASS, null, values)>0);
	}
	
	public boolean deleteSetting(Long ssid){
		SQLiteDatabase db=this.getReadableDatabase();
		return (db.delete(SETTINGS, SET_SESSION_ID+"=" +ssid.toString(), null)>0);
	}
	public boolean deleteSettingMainAll(){
		SQLiteDatabase db=this.getWritableDatabase();
		return (db.delete(SETTINGS_MAIN, null, null)>0);
	}
	public boolean deleteRemPass(Long ssid){
		SQLiteDatabase db=this.getWritableDatabase();
		return (db.delete(REM_PASS, REMPASS_SESSION_ID+"=" +ssid.toString(), null)>0);
	}
	public boolean checkExistsID(Long id){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		try{
			c=db.query(SETTINGS, new String[]{"setting_id","set_session_id","background","fontsize"}, 
					"set_session_id="+id.toString(), null, null, null, null);
			if(!c.equals(null))
				return c.moveToFirst();
		}catch (Exception e) {
			
		}finally{
			c.close();
		}
		return false;
	}
	public boolean checkExistsIDRem(Long id){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		try{
			c=db.query(REM_PASS, new String[]{"rem_pass_id","rempass_session_id","ckrem"}, 
					"rempass_session_id="+id.toString(), null, null, null, null);
			if(!c.equals(null))
				return c.moveToFirst();
		}catch (Exception e) {
		}finally{
			c.close();
		}
		return false;
	}
	public boolean checkExistsIDSession(Long id){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		try{
			c=db.query(SESSION, new String[]{"session_id","hostname","port","username","password","time_"}, 
					"session_id="+id.toString(), null, null, null, null);
			if(!c.equals(null))
				return c.moveToFirst();
		}catch (Exception e) {
		}finally{
			c.close();
		}
		return false;
	}
	public List<Settings> getSettings(Long ssid){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		ArrayList<Settings> settings=new ArrayList<Settings>();
		try{
			c=db.query(SETTINGS, new String[]{"setting_id","set_session_id","background","fontsize"}, 
					"set_session_id="+ssid.toString(), null, null, null, null);
			int numRows=c.getCount();
			c.moveToFirst();
			for(int i=0;i<numRows;i++){
				Settings stt=new Settings();
				stt.setSetting_id(c.getLong(0));
				stt.setSet_session_id(c.getLong(1));
				stt.setBackground(c.getString(2));
				stt.setFontsize(c.getString(3));
				settings.add(stt);
				c.moveToNext();
			}
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			c.close();
		}
		return settings;
	}
	public List<Settings_Main> getSettingsMain(){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		ArrayList<Settings_Main> set_main=new ArrayList<Settings_Main>();
		try{
			c=db.query(SETTINGS_MAIN, new String[]{"settings_main_id","background_","language_"}, null, null, null, null, null);
			int numRows=c.getCount();
			c.moveToFirst();
			for(int i=0;i<numRows;i++){
				Settings_Main stm=new Settings_Main();
				stm.setId(c.getLong(0));
				stm.setBackground(c.getString(1));
				stm.setLanguage(c.getString(2));
				set_main.add(stm);
				c.moveToNext();
			}
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			c.close();
		}
		return set_main;
	}
	public String getCKRemByID(Long id){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		String ck="";
		try{
			c=db.query(REM_PASS, new String[]{"rem_pass_id","rempass_session_id","ckrem"}, 
					"rempass_session_id="+id.toString(), null, null, null, null);
			c.moveToFirst();
			ck=c.getString(c.getColumnIndex("ckrem"));
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			c.close();
		}
		return ck;
	}
	public List<CKRemPass> getCheck(Long id){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=null;
		ArrayList<CKRemPass> list=new ArrayList<CKRemPass>();
		try{
			c=db.query(REM_PASS, new String[]{"rem_pass_id","rempass_session_id","ckrem"}, null, null, null, null, null);
			int numRows=c.getCount();
			c.moveToFirst();
			for(int i=0;i<numRows;i++){
				CKRemPass ck=new CKRemPass();
				ck.setId(c.getLong(0));
				ck.setSsid(c.getLong(1));
				ck.setCheck(c.getString(2));
				list.add(ck);
				c.moveToNext();
			}
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			c.close();
		}
		return list;
	}
	public int UpdateCheckRemPass(Long ssid,String ck){System.out.println(ssid+"----up----"+ck);
		SQLiteDatabase db=this.getWritableDatabase();
		ContentValues values=new ContentValues();
		values.put(CKREM, ck);
		return db.update(REM_PASS, values, REMPASS_SESSION_ID+"=?", new String[]{String.valueOf(ssid)});
	}
}
